 1.SQL实现
   
   1).按年统计销售额
   SELECT year(B.dt) year, round(sum(A.amount)/10000, 2) amount
 FROM saledetail A join sale B on A.orderid=B.orderid
group by year(B.dt);
   2).销售金额在 10 W 以上的订单
   SELECT orderid, round(sum(amount), 2) amount
   from saledetail
GROUP BY orderid
HAVING sum(amount) > 100000;
   3).每年销售额的差值
   SELECT year, round(amount, 2) amount,
       round(lag(amount) OVER (ORDER BY year), 2) prioramount,
       round(amount - lag(amount) OVER (ORDER BY year), 2) diff
  from (SELECT year(B.dt) year, sum(a.amount) amount
          FROM saledetail A JOIN sale B ON A.orderid= B.orderid
        GROUP BY year(B.dt)
        ) tmp;
   4).年度订单金额前10位（年度、订单号、订单金额、排名）
   --方法1
   SELECT dt, orderid, amount, rank
 from (SELECT dt, orderid, amount,
       dense_rank() over(PARTITION BY dt ORDER BY
amount desc) rank
     from (SELECT year(B.dt) dt, A.orderid, sum(A.amount)
amount
         from saledetail A join sale B on
A.orderid=B.orderid
        GROUP BY year(B.dt), A.orderid
        ) tmp1
    ) tmp2
where rank <= 10;
    -- 方法二
	with tmp as (
SELECT year(B.dt) dt, A.orderid, sum(A.amount) amount
 from saledetail A join sale B on A.orderid=B.orderid
GROUP BY year(B.dt), A.orderid
)
SELECT dt, orderid, amount, rank
 from (SELECT dt, orderid, amount,
       dense_rank() over(PARTITION BY dt ORDER BY
amount desc) rank
     from tmp
    ) tmp2
where rank <= 10;
   5).季度订单金额前10位（年度、季度、订单id、订单金额、排名）
   -- 方法一 
WITH tmp as (
SELECT c.year, c.quat, a.orderid,  round(sum(b.amount), 2) amount
    FROM sale a JOIN saledetail b ON a.orderid = b.orderid
                JOIN dimdate c    ON a.dt=c.dt
    GROUP BY c.year, c.quat, a.orderid
)
SELECT year, quat, orderid, amount, rank 
  from (SELECT year, quat, orderid, amount,
               dense_rank() OVER(PARTITION BY year, quat ORDER BY amount desc ) rank
        FROM tmp
       ) tmp1
WHERE rank <=10;
   -- 方法二
   WITH tmp as (
SELECT year(a.dt) year,
       CASE when month(a.dt) <=3 then 1
            when month(a.dt) <=6 then 2
            when month(a.dt) <=9 then 3
            else 4 end quat,
       a.orderid,
       round(sum(b.amount), 2) amount
  from sale a JOIN saledetail b ON a.orderid = b.orderid
GROUP BY year(a.dt),
         CASE when month(a.dt) <= 3 then 1
              when month(a.dt) <= 6 then 2
              when month(a.dt) <= 9 then 3
              else 4 end,
         a.orderid
)
SELECT year, quat, orderid, amount, rank 
  from (SELECT year, quat, orderid, amount,
               dense_rank() OVER(PARTITION BY year, quat ORDER BY amount desc ) rank
        FROM tmp
       ) tmp1
WHERE rank <=10;
    -- 方法三。求季度
	SELECT floor(month(dt)/3.1) + 1 ;

WITH tmp as (
SELECT year(a.dt) year,
       floor(month(dt)/3.1) + 1 quat,
       a.orderid,
       round(sum(b.amount), 2) amount
  from sale a JOIN saledetail b ON a.orderid = b.orderid
GROUP BY year(a.dt),
         floor(month(dt)/3.1) + 1,
         a.orderid
)
SELECT year, quat, orderid, amount, rank 
  from (SELECT year, quat, orderid, amount,
               dense_rank() OVER(PARTITION BY year, quat ORDER BY amount desc ) rank
        FROM tmp
       ) tmp1
WHERE rank <=10;
  
  6).求所有交易日中订单金额最高的前10位
  topN问题：
  (1)基础数据
  (2)上排名函数
  (3)解决N的问题
  WITH tmp as (
SELECT a.dt, a.orderid, round(sum(b.amount), 2) amount
   FROM sale a JOIN saledetail b ON a.orderid = b.orderid
GROUP BY a.dt, a.orderid
)
SELECT dt, orderid, amount,rank
  FROM (
       SELECT dt, orderid, amount,dense_rank() OVER(order by amount desc) rank 
       FROM tmp
       ) tmp1
where rank <= 10;
   
   7).每年度销售额最大的交易日
    WITH tmp as (
SELECT a.dt, round(sum(b.amount), 2) amount
   FROM sale a JOIN saledetail b ON a.orderid = b.orderid
GROUP BY a.dt
)
select year(dt) year, max(amount) dayamount from tmp
GROUP BY  year(dt)
   8).年度最畅销的商品(即每年销售金额最大的商品)
   WITH tmp as (
SELECT year(b.dt) year, goods, round(sum(amount), 2) amount
  from saledetail a JOIN sale b ON a.orderid = b.orderid
GROUP BY year(b.dt), goods
)
SELECT year, goods, amount
  FROM (SELECT year, goods, amount, dense_rank() OVER(PARTITION BY year ORDER BY amount desc) rank
        FROM tmp) tmp1
WHERE rank =1

       
   